package ltd.nullpointer.frontend.dao;

import com.boot2.core.dao.jpa.AbsctractDao;
import ltd.nullpointer.frontend.model.entity.up.SensorData;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

//@DataSource("test2")
public interface SensorDataDao extends AbsctractDao<SensorData> {


        /**
     * 用于去重
     * @param sensorNo
     * @param measuringTime
     * @return
     */
    boolean existsBySensorNoAndMeasuringTime(String sensorNo, Date measuringTime);

    /**
     * 删除某个设备的所有数据
     * @param sensorNo
     * @return
     */
    @Modifying
    @Query("delete from SensorData t where t.sensorNo =?1 and t.measuringDate > ?2 and t.measuringDate < ?3")
    @Transactional
    void deleteBySensorNo(String sensorNo, Date timeFrom, Date timeTo);

    /**
     * 删除某个设备的具体某个时间点的值
     * @param sensorNo
     * @param date
     * @return
     */
    @Modifying
    @Query("delete from SensorData t where t.sensorNo =?1 and t.measuringDate = ?2")
    @Transactional
    void deleteBySensorNoAndDate(String sensorNo, Date date);

    /**
     * 测量值最大值
     * @param sensorNo
     * @param date
     * @return
     */
    @Query("select t from SensorData t where t.sensorNo =?1 and t.measuringDate > ?2 and t.measuringDate < ?3")
    List<SensorData> getSensorData(String sensorNo, Date dateFrom, Date dataTo);

    /**
     * 测量值最大值
     * @param sensorNo
     * @param date
     * @return
     */
    @Query("select t from SensorData t where t.sensorNo =?1 and t.measuringDate = ?2")
    SensorData findSensorData(String sensorNo, Date date);

    /**
     * 测量值最大值
     * @param sensorNo
     * @param date
     * @return
     */
    @Query("select max(measurement) from SensorData t where t.sensorNo =?1 and t.measuringDate = ?2")
    BigDecimal findMaxMeasurement(String sensorNo, Date date);

    /**
     * 测量值最小值
     * @param sensorNo
     * @param date
     * @return
     */
    @Query("select min(measurement) from SensorData t where t.sensorNo =?1 and t.measuringDate = ?2")
    BigDecimal findMinMeasurement(String sensorNo, Date date);

    /**
     * 偏移值最大值
     * @param sensorNo
     * @param date
     * @return
     */
    @Query("select max(deviation) from SensorData t where t.sensorNo =?1 and t.measuringDate = ?2")
    BigDecimal findMaxDeviation(String sensorNo, Date date);

    /**
     * 偏移值最小值
     * @param sensorNo
     * @param date
     * @return
     */
    @Query("select min(deviation) from SensorData t where t.sensorNo =?1 and t.measuringDate = ?2")
    BigDecimal findMinDeviation(String sensorNo, Date date);

}